## 6.4. Indices

While MySQL and mSQL both have greater performance than any of the larger database servers, some problems still call for careful database design. For instance, if we had a table with millions of rows of data, a search for a specific row would take a long time. As we discussed in Chapter 2, "Database Design", most database engines enable you to help it in these searches through a tool called an index.

Indices help the database store data in a way that makes for quicker searches. Unfortunately, you sacrifice disk space and modification speed for the benefit of quicker searches. The most efficient use of indices is to create an index for columns on which you tend to search the most. MySQL and mSQL support a common syntax for index creation:

>     CREATE INDEX index_name ON tablename (column1,
>                                           column2,
>                                           ...,
>                                           columnN)

MySQL also lets you create an index at the same time you create a table using the following syntax:

>     CREATE TABLE materials (id         INT      NOT NULL,
>                             name       CHAR(50) NOT NULL,
>                             resistance INT,
>                             melting_pt REAL,
>                             INDEX index1 (id, name),
>                             UNIQUE INDEX index2 (name))

The previous example creates two indices for the table. The first index -- named `index1` -- consists of both the `id` and `name` fields. The second index includes only the `name` field and specifies that values for the `name` field must always be unique. If you try to insert a field with a `name` held by a row already in the database, the insert will fail. All fields declared in a unique index must be declared as being `NOT NULL` .

Even though we created an index for `name` by itself, we did not create an index for just `id`. If we did want such an index, we would not need to create it -- it is already there. When an index contains more than one column (for example: `name`, `rank`, and `serial_number`), MySQL reads the columns in order from left to right. Because of the structure of the index MySQL uses, any subset of the columns from left to right are automatically created as indices within the "main" index. For example, `name` by itself and `name` and `rank` together are both "free" indices created when you create the index `name`, `rank`, `serial_number`. An index of `rank` by itself or `name` and `serial_number` together, however, is not created unless you explicitly create it yourself.

MySQL also supports the ANSI SQL semantics of a special index called a primary key. In MySQL, a primary key is a unique key with the name `PRIMARY`. By calling a column a primary key at creation, you are naming it as a unique index that will support table joins. The following example creates a `cities` table with a primary key of `id`.

>     CREATE TABLE cities (id      INT  NOT NULL PRIMARY KEY,
>                          name    VARCHAR(100),
>                          pop     MEDIUMINT,
>                          founded DATE)

Before you create a table, you should determine which fields, if any, should be keys. As we mentioned above, any fields which will be supporting joins are good candidates for primary keys. See Chapter 2, "Database Design" for a detailed discussion on how to design your tables with good primary keys.

Copyright © 2001 O'Reilly & Associates. All rights reserved.